﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Globalization;
using LumenWorks.Framework.IO.Csv;
using System.Diagnostics.CodeAnalysis;
using System.Diagnostics.Contracts;
using System.Data;

namespace GoogleTransit
{
    /// <summary>
    /// /// Converts the raw text files in the format google accepts to a database for easy retrieval
    /// </summary>
    [System.Diagnostics.Contracts.ContractVerification(false)]
    public class Converter
    {
        private void execNonQuery(String command, IDbConnection connection)
        {
            using (IDbCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = command;
                cmd.ExecuteNonQuery();
            }
        }

        private void reset(ScheduleInfo db)
        {
            IDbConnection conn = db.Connection;
            System.Console.Out.WriteLine("Resetting database");
            System.Console.Out.WriteLine("Deleting Bus Stops");
            execNonQuery("delete from BusStops", conn);
            System.Console.Out.WriteLine("Deleting Departures");
            execNonQuery("delete from Departures", conn);
            System.Console.Out.WriteLine("Deleting Routes");
            execNonQuery("delete from Routes", conn);
            System.Console.Out.WriteLine("Deleting Services");
            execNonQuery("delete from Service", conn);
            System.Console.Out.WriteLine("Deleting Servoce Dates");
            execNonQuery("delete from serviceDates", conn);
            System.Console.Out.WriteLine("Vacuuming");
            execNonQuery("vacuum", conn);
            System.Console.Out.WriteLine("database is reset");
        }

        private int addDates(ScheduleInfo db, Service service, DateTime start, DateTime end, bool monday, bool tuesday, bool wednesday, bool thursday, bool friday, bool saturday, bool sunday)
        {
            Contract.Requires(db != null);
            Contract.Requires(service != null);
            int numAdded = 0;
            for (DateTime date = start; date <= end; date = date.AddDays(1))
            {
                switch (date.DayOfWeek)
                {
                    case DayOfWeek.Monday:
                        if (monday)
                        { db.ServiceDates.InsertOnSubmit(new ServiceDates() { Service = service, Date = date }); numAdded++; }
                        break;
                    case DayOfWeek.Tuesday:
                        if (tuesday)
                        { db.ServiceDates.InsertOnSubmit(new ServiceDates() { Service = service, Date = date }); numAdded++; }
                        break;
                    case DayOfWeek.Wednesday:
                        if (wednesday)
                        { db.ServiceDates.InsertOnSubmit(new ServiceDates() { Service = service, Date = date }); numAdded++; }
                        break;
                    case DayOfWeek.Thursday:
                        if (thursday)
                        { db.ServiceDates.InsertOnSubmit(new ServiceDates() { Service = service, Date = date }); numAdded++; }
                        break;
                    case DayOfWeek.Friday:
                        if (friday)
                        { db.ServiceDates.InsertOnSubmit(new ServiceDates() { Service = service, Date = date }); numAdded++; }
                        break;
                    case DayOfWeek.Saturday:
                        if (saturday)
                        { db.ServiceDates.InsertOnSubmit(new ServiceDates() { Service = service, Date = date }); numAdded++; }
                        break;
                    case DayOfWeek.Sunday:
                        if (sunday)
                        { db.ServiceDates.InsertOnSubmit(new ServiceDates() { Service = service, Date = date }); numAdded++; }
                        break;
                    default:
                        //WTF?
                        break;
                }
            }
            return numAdded;
        }

        /// <summary>
        /// Converts the raw text files in the format google accepts to a database for easy retrieval
        /// </summary>
        /// <param name="transitDB">The database to store the data in</param>
        /// <param name="dir">The directory containing the text files</param>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Reliability", "CA2000:Dispose objects before losing scope")]
        public void Convert(ScheduleInfo transitDB, DirectoryInfo dir)
        {
            Contract.Requires(transitDB != null);
            Contract.Requires(dir != null);
            Contract.Requires(dir.Exists);
            reset(transitDB);

            Dictionary<String, String> routeNumbers = new Dictionary<string, string>();
            Dictionary<String, Service> scheduleNames = new Dictionary<string, Service>();
            Dictionary<String, Routes> routeNames = new Dictionary<string, Routes>();
            Dictionary<String, Routes> tripToBusMappings = new Dictionary<string, Routes>();
            Dictionary<String, Service> tripToScheduleMappings = new Dictionary<string, Service>();
            Dictionary<String, BusStops> stops = new Dictionary<string, BusStops>();
            CultureInfo nfo = new CultureInfo("en-US");
            String dateFormat = "yyyyMMdd";

            using (CsvReader reader = new CsvReader(new StreamReader(getFile(dir, "routes.txt")), true))
            {
                int routeField = reader.GetFieldIndex("route_id");
                int nameField = reader.GetFieldIndex("route_short_name");
                while (reader.ReadNextRecord())
                {
                    routeNumbers[reader[routeField]] = reader[nameField];
                }
            }

            using (CsvReader reader = new CsvReader(new StreamReader(getFile(dir, "calendar.txt")), true))
            {
                int serviceField = reader.GetFieldIndex("service_id");
                int mondayField = reader.GetFieldIndex("monday");
                int tuesdayField = reader.GetFieldIndex("tuesday");
                int wednesdayField = reader.GetFieldIndex("wednesday");
                int thursdayField = reader.GetFieldIndex("thursday");
                int fridayField = reader.GetFieldIndex("friday");
                int saturdayField = reader.GetFieldIndex("saturday");
                int sundayField = reader.GetFieldIndex("sunday");
                int startField = reader.GetFieldIndex("start_date");
                int endField = reader.GetFieldIndex("end_date");
                while (reader.ReadNextRecord())
                {
                    String name = reader[serviceField];
                    Service schedule = new Service() { Name = name };
                    transitDB.Service.InsertOnSubmit(schedule);
                    transitDB.SubmitChanges();
                    scheduleNames[name] = schedule;
                    int numDays = addDates(transitDB,
                        schedule,
                        DateTime.ParseExact(reader[startField], dateFormat, nfo),
                        DateTime.ParseExact(reader[endField], dateFormat, nfo),
                        int.Parse(reader[mondayField]) != 0,
                        int.Parse(reader[tuesdayField]) != 0,
                        int.Parse(reader[wednesdayField]) != 0,
                        int.Parse(reader[thursdayField]) != 0,
                        int.Parse(reader[fridayField]) != 0,
                        int.Parse(reader[saturdayField]) != 0,
                        int.Parse(reader[sundayField]) != 0);
                    System.Console.Out.WriteLine("Added " + numDays + " days to the service");
                }
            }
            transitDB.SubmitChanges();
            using (CsvReader reader = new CsvReader(new StreamReader(getFile(dir, "calendar_dates.txt")), true))
            {
                int serviceField = reader.GetFieldIndex("service_id");
                int dateField = reader.GetFieldIndex("date");
                int exceptionTypeField = reader.GetFieldIndex("exception_type");
                while (reader.ReadNextRecord())
                {
                    int exType = int.Parse(reader[exceptionTypeField]);
                    if (exType == 1)
                    {
                        Service service = scheduleNames[reader[serviceField]];
                        transitDB.ServiceDates.InsertOnSubmit(new ServiceDates() { Service = service, Date = DateTime.ParseExact(reader[dateField], dateFormat, nfo) });
                    }
                    else if (exType == 2)
                    {
                        Service service = scheduleNames[reader[serviceField]];
                        DateTime dateToRemove = DateTime.ParseExact(reader[dateField], dateFormat, nfo);
                        var badDate = from sd in transitDB.ServiceDates
                                      where sd.ServiceID == service.ID && sd.Date == dateToRemove
                                      select sd;
                        var removeThis = badDate.First();
                        transitDB.ServiceDates.DeleteOnSubmit(removeThis);
                    }
                }
            }
            transitDB.SubmitChanges();
            System.Console.Out.WriteLine("Finding trip references");
            using (CsvReader reader = new CsvReader(new StreamReader(getFile(dir, "trips.txt")), true))
            {
                int routeField = reader.GetFieldIndex("route_id");
                int tripField = reader.GetFieldIndex("trip_id");
                int nameField = reader.GetFieldIndex("trip_headsign");
                int serviceField = reader.GetFieldIndex("service_id");
                while (reader.ReadNextRecord())
                {
                    String routeId = reader[routeField];
                    String tripId = reader[tripField];
                    String number = routeNumbers[routeId];
                    String name = reader[nameField];
                    String combinedName = number + "$" + name;
                    if (!routeNames.ContainsKey(combinedName))
                    {
                        Routes route = new Routes() { Code = number, Name = name };
                        routeNames[combinedName] = route;
                        transitDB.Routes.InsertOnSubmit(route);
                    }
                    tripToBusMappings[tripId] = routeNames[combinedName];
                    tripToScheduleMappings[tripId] = scheduleNames[reader[serviceField]];
                }
            }

            using (CsvReader reader = new CsvReader(new StreamReader(getFile(dir, "stops.txt")), true))
            {
                int stopId = reader.GetFieldIndex("stop_id");
                int stopCode = reader.GetFieldIndex("stop_code");
                int stopName = reader.GetFieldIndex("stop_name");
                int stopLat = reader.GetFieldIndex("stop_lat");
                int stopLon = reader.GetFieldIndex("stop_lon");
                while (reader.ReadNextRecord())
                {
                    BusStops stop = new BusStops()
                    {
                        Name = reader[stopName],
                        Code = reader[stopCode],
                        Latitude = decimal.Parse(reader[stopLat]),
                        Longitude = decimal.Parse(reader[stopLon])
                    };
                    transitDB.BusStops.InsertOnSubmit(stop);
                    stops[reader[stopId]] = stop;
                }
            }
            System.Console.Out.WriteLine("Adding " + stops.Count + " Stops");
            transitDB.SubmitChanges();

            int numberOfStopTimeEntries = 0;
            using (StreamReader sr = new StreamReader(getFile(dir, "stop_times.txt")))
            {
                while (sr.ReadLine() != null)
                { numberOfStopTimeEntries++; }
            }
            System.Console.Out.WriteLine("Adding " + numberOfStopTimeEntries + " individual departure events");
            int i = 0;
            DateTime startTime = DateTime.Now;
            
            using (CsvReader reader = new CsvReader(new StreamReader(getFile(dir, "stop_times.txt")), true))
            {
                int tripId = reader.GetFieldIndex("trip_id");
                int arrivalTime = reader.GetFieldIndex("arrival_time");
                int stopId = reader.GetFieldIndex("stop_id");
                using (IDbTransaction trans = transitDB.Connection.BeginTransaction(IsolationLevel.Serializable))
                using (IDbCommand cmd = transitDB.Connection.CreateCommand())
                {
                    StringBuilder sb = new StringBuilder();
                    while (reader.ReadNextRecord())
                    {
                        i++;
                        if (sb.Length > 12800)
                        {
                            cmd.CommandText = sb.ToString();
                            cmd.ExecuteNonQuery();
                            sb.Length = 0;

                            double percentComplete = Math.Round((double)i / (double)numberOfStopTimeEntries * 10000) / 100;
                            System.Console.Out.WriteLine("Still alive " + percentComplete + "%, Elapsed Time " + DateTime.Now.Subtract(startTime));
                        }

                        String trip = reader[tripId];
                        Service sched = tripToScheduleMappings[trip];
                        Routes bus = tripToBusMappings[trip];
                        BusStops bs = stops[reader[stopId]];

                        //Departures d = new Departures() { Service = sched, BusStops = bs, Routes = bus, Time = convertTime(reader[arrivalTime]) };
                        //transitDB.Departures.InsertOnSubmit(d);

                        sb.Append("insert into Departures(BusStopId, RouteId, ServiceId, Time) values");
                        sb.Append("(");
                        sb.Append(bs.ID);
                        sb.Append(",");
                        sb.Append(bus.ID);
                        sb.Append(",");
                        sb.Append(sched.ID);
                        sb.Append(",");
                        sb.Append(convertTime(reader[arrivalTime]));
                        sb.Append(");");
                    }
                    cmd.CommandText = sb.ToString();
                    cmd.ExecuteNonQuery();
                    sb.Length = 0;

                    cmd.CommandText = "insert into RouteStops select distinct BusStopId, RouteId from Departures;";
                    cmd.ExecuteNonQuery();

                    trans.Commit();
                    sb.Length = 0;


                }
                transitDB.SubmitChanges();
                System.Console.Out.WriteLine("All done :)");
            }
        }

        /// <summary>
        /// Converts a string representation of a timespan into the equivalent number of minutes
        /// </summary>
        /// <param name="time">A string containing a timespan</param>
        /// <returns>The number of minutes represented by the timespan</returns>
        private int convertTime(String time)
        {
            if (time == null) { return -1; }
            String[] parts = time.Split(':');
            int h = int.Parse(parts[0]);
            if (parts.Length < 2)
            {
                return h * 60;
            }
            else
            {
                int m = int.Parse(parts[1] ?? "0");
                return h * 60 + m;
            }
        }

        /// <summary>
        /// Returns the path+name of the file in the directory
        /// </summary>
        /// <param name="dir">The directory the file is in</param>
        /// <param name="filename">The name of the file</param>
        /// <returns>the path+name of the file in the directory</returns>
        private String getFile(DirectoryInfo dir, String filename)
        {
            Contract.Requires(dir != null);
            Contract.Requires(filename != null);
            return Path.Combine(dir.FullName, filename);
        }
    }
}
